CREATE LANGUAGE 'plpgsql';

CREATE SEQUENCE objectid INCREMENT 1 MINVALUE 1000 MAXVALUE 9223372036854775807 START 1000;

CREATE OR REPLACE FUNCTION areflagsoff (integer, integer) RETURNS boolean AS '
    BEGIN
        RETURN ($1 & $2) = 0;
    END;
' LANGUAGE 'plpgsql';

CREATE OR REPLACE FUNCTION areflagson (integer, integer) RETURNS boolean AS '
    BEGIN
        RETURN ($1 & $2) > 0;
    END;
' LANGUAGE 'plpgsql';




CREATE TABLE site
(
	id						INTEGER			NOT NULL,
	name					VARCHAR(64),
	is_default				BOOLEAN			DEFAULT false,
	domains					TEXT			DEFAULT null,
	attributes				TEXT			DEFAULT null,

	PRIMARY KEY(id)
);



CREATE TABLE adminuser
(
	id						INTEGER			DEFAULT nextval('objectid'),
	username				VARCHAR(32)		NOT NULL,
	password				VARCHAR(32)		NOT NULL,
	name					VARCHAR(64),
	email					VARCHAR(64),
	rights					INTEGER			DEFAULT 0,
	attributes				TEXT,
	
	PRIMARY KEY(username)
);


CREATE TABLE siteuser
(
	id						INTEGER			NOT NULL,
	site_id					INTEGER			NOT NULL REFERENCES site(id) ON DELETE CASCADE,
	name					VARCHAR(64),
	gender					CHAR(1),
	birthyear				INTEGER,
	email					VARCHAR(128),
	password				VARCHAR(32)		NOT NULL,
	type					INTEGER,
	rights					INTEGER,
	status					INTEGER			NOT NULL DEFAULT 0,
	reg_id					VARCHAR(10),
	language				CHAR(2),
	created_at				TIMESTAMP		DEFAULT current_timestamp,
	attributes				TEXT,
	texts					TEXT,
	
	PRIMARY KEY(id)
);
CREATE INDEX emailindex ON siteuser (email);
CREATE INDEX usernameindex ON siteuser (name);



CREATE TABLE menu_group
(
	id						INTEGER			NOT NULL,
	name					VARCHAR(64),
	site_id					INTEGER			DEFAULT 1,
	is_removeable			BOOLEAN,
	max_level				INT2			DEFAULT 0,
	attributes				TEXT			DEFAULT null,	

	PRIMARY KEY(id)
);

CREATE TABLE menu
(
	id						INTEGER			NOT NULL,
	parentid				INTEGER,
	menu_group_id			INTEGER,
	site_id					INTEGER			DEFAULT 1,
	is_default				BOOLEAN,
	langcode				CHAR(2),
	label					VARCHAR(128)	NOT NULL,
	css						VARCHAR(32),
	serial					INT2,
	target_type				INT2,
	article_id				INTEGER,
	url						VARCHAR(1024),
	email					VARCHAR(128),
	file					VARCHAR(256),
	function				VARCHAR(64),
	is_popup				BOOLEAN,
	popup_props				VARCHAR(128),
	attributes				TEXT			DEFAULT null,	

	PRIMARY KEY(id)
);


CREATE TABLE article
(
	id						INTEGER			NOT NULL,
	id_name					VARCHAR(32),
	langcode				CHAR(2)			NOT NULL,
	site_id					INTEGER			DEFAULT 1,
	type					INT2,
	subtype					INT2			DEFAULT 0,
	worktitle				VARCHAR(128),
	title					VARCHAR(128),
	description				TEXT,
	modifiedat				TIMESTAMP,
	keywords				TEXT,
	lead					TEXT,
	body					TEXT,
	publishdate				TIMESTAMP,
	attributes				TEXT,
	

	PRIMARY KEY(id, langcode)
);


-- cikk folder rendszer
-- ha id_parent nem null, akkor az id_parent azonos�t�j� folder a sz�l� folder, �s a site_id, langcode �s type nem �rdekes
-- ha id_parent null, akkor a ez egy gy�k�r foldere a (site_id, langcode, type) h�rmas �ltal meghat�rozott cikkeknek
CREATE TABLE article_folder
(
	id						INTEGER			NOT NULL,
	id_parent				INTEGER			REFERENCES article_folder(id) ON DELETE SET NULL,
	langcode				CHAR(2),
	site_id					INTEGER,
	type					INT2,
	name					VARCHAR(128)	NOT NULL,

	PRIMARY KEY(id)
);

-- a langcode kell a foreign key miatt!
CREATE TABLE articlefolder_article(
	id						INTEGER			NOT NULL PRIMARY KEY,
	id_article				INTEGER,
	langcode				CHAR(2),
	id_folder				INTEGER			REFERENCES article_folder(id) ON DELETE CASCADE,

	FOREIGN KEY (id_article, langcode) REFERENCES article(id,langcode) ON DELETE CASCADE
);

CREATE TABLE galery
(
	id						INTEGER			NOT NULL,
	langcode				CHAR(2)			NOT NULL,
	site_id					INTEGER			DEFAULT 1,
	worktitle				VARCHAR(64),
	file_ids				TEXT,						-- fix sorrendhelyesen a file-ok azonos�t�i
	attributes				TEXT,
	
	PRIMARY KEY(id)
);


CREATE TABLE galery_file
(
	id						INTEGER			NOT NULL,
	galery_id				INTEGER			NOT NULL REFERENCES galery(id) ON DELETE CASCADE ON UPDATE CASCADE,
	file_path				VARCHAR(128),
	attributes				TEXT,

	PRIMARY KEY(id)
);


CREATE TABLE language
(
	langcode				CHAR(2)			NOT NULL,
	site_id					INTEGER			DEFAULT 1,
	is_default				BOOLEAN,
	name					VARCHAR(64),
	attributes				TEXT,	

	PRIMARY KEY(langcode, site_id)
);


CREATE TABLE sitetext
(
	txtkey					VARCHAR(64)		NOT NULL,
	langcode				CHAR(2)			NOT NULL,
	site_id					INTEGER			DEFAULT 1,
	txt						TEXT,
	inc_to_template			BOOLEAN			DEFAULT false,
	is_shared				BOOLEAN			DEFAULT false,
	load_allang				BOOLEAN			DEFAULT false,
	type					INT2,
	comment					TEXT,

	PRIMARY KEY (txtkey, langcode, site_id)
);


CREATE TABLE db_lock
(
	key						VARCHAR(128)	NOT NULL,
	lock_id					INTEGER,
	created_at				TIMESTAMP		DEFAULT now(),
	
	PRIMARY KEY(key)
);


CREATE TABLE thread
(
	name					VARCHAR(32)		NOT NULL,
	object					TEXT,
	created					TIMESTAMP,
	
	PRIMARY KEY(name)
);

CREATE TABLE thread_command
(
	thread_name				VARCHAR(32)		NOT NULL REFERENCES thread(name) ON DELETE CASCADE,
	id						INTEGER			NOT NULL,
	command					TEXT,
	created					TIMESTAMP,
	
	PRIMARY KEY(thread_name, id)
);

CREATE TABLE thread_response
(
	thread_name				VARCHAR(32)		NOT NULL REFERENCES thread(name) ON DELETE CASCADE,
	command_id				INTEGER			NOT NULL,
	response				TEXT,
	created					TIMESTAMP,
	
	PRIMARY KEY(thread_name, command_id)
);


CREATE TABLE setting
(
	name					VARCHAR(64)		NOT NULL,
	langcode				VARCHAR(2)		DEFAULT '',
	site_id					INTEGER			DEFAULT -1,
	valuetype				INT2			NOT NULL,
	strvalue				VARCHAR(128),
	intvalue				INTEGER,
	
	PRIMARY KEY(name, langcode, site_id)
);


CREATE TABLE forumtopic(
	id INTEGER NOT NULL PRIMARY KEY,
	langcode				CHAR(2),
	site_id					INTEGER,

	parent_object_id INTEGER,
	parent_object_type VARCHAR(64),						-- ez lehet pl. a tabla neve amiben az id van...
	name VARCHAR(32) NOT NULL,
	
	attributes TEXT,

	UNIQUE(parent_object_id, parent_object_type, name)
);

CREATE TABLE forummessage(
	id_forumtopic INTEGER NOT NULL REFERENCES forumtopic(id) ON DELETE CASCADE,
	serial INTEGER,
	id_user INTEGER NOT NULL REFERENCES siteuser(id) ON DELETE CASCADE,
	parent_serial INTEGER,
	child_serials TEXT,
	insdate TIMESTAMP DEFAULT current_timestamp,
	level SMALLINT,
	message TEXT,
	is_moderated BOOLEAN DEFAULT false,
	attributes TEXT,
	
	PRIMARY KEY(id_forumtopic, serial)
);


CREATE TABLE ftp_users
(
	name					VARCHAR(64)		NOT NULL,

	PRIMARY KEY (name)
);

CREATE TABLE session(
	id VARCHAR(64),
	user_id INTEGER,
	created TIMESTAMP,
	last_access TIMESTAMP,
	login_time TIMESTAMP,
	logout_time TIMESTAMP,
	ip_address VARCHAR(32),
	
	PRIMARY KEY(id, user_id)
);

-----------------------------------------------------------------
-- Boxes
-----------------------------------------------------------------

CREATE TABLE box
(
	id						INTEGER			NOT NULL,
	langcode				CHAR(2)			NOT NULL,
	site_id					INTEGER			REFERENCES site(id) ON DELETE CASCADE,
	name					VARCHAR(64),
	height					INT2,
	closed					BOOLEAN			NOT NULL DEFAULT false,
	closable				BOOLEAN			NOT NULL DEFAULT true,
	noheader				BOOLEAN			NOT NULL DEFAULT false,
	place					INT2,
	css						VARCHAR(32),
	serial					INT2,

	PRIMARY KEY(id)
);


CREATE TABLE box_tab
(
	id						INTEGER			NOT NULL,
	box_id					INTEGER			NOT NULL REFERENCES box(id) ON DELETE CASCADE,
	label					VARCHAR(64),
	isdefault				BOOLEAN,
	serial					INT2,

	PRIMARY KEY(id)
);



CREATE TABLE box_content_list
(
	id						INTEGER			NOT NULL,
	box_tab_id				INTEGER			NOT NULL REFERENCES box_tab(id) ON DELETE CASCADE,
	name					VARCHAR(64),
	box_article_ids			TEXT,						-- ez egy szerializált php tömb lesz
	isdefault				BOOLEAN,
	class					VARCHAR(32)		DEFAULT 'BoxContentList',
	type					INT2,
	manualstep				BOOLEAN 		NOT NULL DEFAULT true,
	autostep				INTEGER,
	sizemin					INT2,						-- autoselect tipusnal van ertelme
	sizemax					INT2,						-- autoselect tipusnal van ertelme
	attributes				TEXT,

	PRIMARY KEY(id)
);


CREATE TABLE article_boxcontentlist_connect
(
	box_content_list_id		INTEGER			NOT NULL REFERENCES box_content_list(id) ON DELETE CASCADE ON UPDATE CASCADE,
	article_id				INTEGER,
	langcode				CHAR(2),

	PRIMARY KEY(box_content_list_id, article_id, langcode)
);

CREATE OR REPLACE VIEW vw_box_tab_list AS
	SELECT 
		b.langcode, b.name as box_name, bt.*
	FROM 
		box b LEFT JOIN 
		box_tab bt ON (b.id = bt.box_id);

CREATE OR REPLACE VIEW vw_box_content_list AS
	SELECT 
		b.langcode, b.name as box_name, bt.box_id, bt.label as box_tab_label, bcl.*
	FROM 
		box b LEFT JOIN 
		box_tab bt ON (b.id = bt.box_id) LEFT JOIN 
		box_content_list bcl ON (bt.id = bcl.box_tab_id);


-----------------------------------------------------------------
-- Mailing
-----------------------------------------------------------------

CREATE SEQUENCE mailid INCREMENT 1 MINVALUE 1000 MAXVALUE 9223372036854775807 START 1000;

CREATE TABLE digest_mail
(
	id						INTEGER			NOT NULL,
	txtkey					VARCHAR(128)	NOT NULL,
	created_at				TIMESTAMP		DEFAULT current_timestamp,

	PRIMARY KEY(id),
	UNIQUE(txtkey)
);
CREATE INDEX txtkeyindex ON digest_mail (txtkey);


CREATE TABLE digest_mail_part
(
	digest_mail_id			INTEGER			NOT NULL REFERENCES digest_mail(id) ON DELETE CASCADE,
	section					VARCHAR(128)	NOT NULL,
	subsection				VARCHAR(128),
	template				VARCHAR(128)	NOT NULL,
	variables				TEXT,
	created_at				TIMESTAMP		DEFAULT current_timestamp,

	PRIMARY KEY(digest_mail_id, section, created_at)
);

-- MailHelper ide pakolja a norm�l leveleket
CREATE TABLE mail
(
	id						INTEGER			DEFAULT nextval('mailid'),
	area					VARCHAR(16),
	type					INT2,
	mail_to					TEXT,
	mail_from				VARCHAR(128),
	reply_to				VARCHAR(128),
	subject					TEXT,
	styles					TEXT,
	html_message			TEXT,
	plain_message			TEXT,
	attachments				TEXT,
	created_at				TIMESTAMP		DEFAULT current_timestamp,
	
	PRIMARY KEY(id)
);

-- SendMailJob ide tudja archiv�lni a kik�ld�tt leveleket
CREATE TABLE mail_sent
(
	id						INTEGER,
	sent_at					TIMESTAMP		DEFAULT current_timestamp,
	success					BOOLEAN			DEFAULT true,
	
	area					VARCHAR(16),
	type					INT2,
	mail_to					TEXT,
	mail_from				VARCHAR(128),
	reply_to				VARCHAR(128),
	subject					TEXT,
	styles					TEXT,
	html_message			TEXT,
	plain_message			TEXT,
	attachments				TEXT,
	created_at				TIMESTAMP,
	
	PRIMARY KEY(id)
);


-- ez a view megmutatja a r�szek sz�moss�g�t is
CREATE OR REPLACE VIEW vw_digest_mail_header AS
	SELECT
		dm.id,
		dm.txtkey,
		dm.created_at,
		count(dmp.*) as part_count
	FROM digest_mail dm, digest_mail_part dmp
	WHERE dm.id=dmp.digest_mail_id
	GROUP BY dm.id, dm.txtkey, dm.created_at;

-----------------------------------------------------------------
-- KIBIC BRIDGE SPECIFIKUS TÁBLÁK
-----------------------------------------------------------------

-- versenyforma
CREATE TABLE tournamenttype(
	id			INTEGER NOT NULL,
	langcode	CHAR(2) NOT NULL,
	name		VARCHAR(127) NOT NULL,
	comment		TEXT,

	PRIMARY KEY(id, langcode)
);

-- verseny
CREATE TABLE tournament(
	id			INTEGER NOT NULL,
	date_from 	TIMESTAMP NOT NULL,
	date_to		TIMESTAMP NOT NULL,
	deadline	TIMESTAMP NOT NULL,
	email		VARCHAR(255),
	default_langcode		CHAR(2) DEFAULT 'en',
	status		SMALLINT DEFAULT 0,

	PRIMARY KEY(id)
);

-- verseny szöveges tartalmak
CREATE TABLE tournament_texts(
	id				INTEGER NOT NULL,
	id_tournament 	INTEGER REFERENCES tournament(id) ON DELETE CASCADE,
	langcode		CHAR(2) NOT NULL,
	name			VARCHAR(255) NOT NULL,
	description		TEXT,
	location		VARCHAR(1024) NOT NULL,
	results			TEXT,

	PRIMARY KEY(id)
);

-- partner kereső
CREATE TABLE partnerfinder(
	id				INTEGER NOT NULL,
	id_tournament 	INTEGER REFERENCES tournament(id) ON DELETE CASCADE,
	id_tournamenttype 	INTEGER NOT NULL,
	langcode		CHAR(2) NOT NULL,
	name			VARCHAR(255) NOT NULL,
	email			VARCHAR(255) NOT NULL,
	description		TEXT,

	PRIMARY KEY(id)
);

CREATE OR REPLACE VIEW vw_tournament AS
	SELECT 
		t.*, tt.langcode, tt.name, tt.description, tt.location, tt.results
	FROM 
		tournament t LEFT JOIN
		tournament_texts tt ON (t.id = tt.id_tournament);

CREATE TABLE tournament_article(
	id_tournament 		INTEGER NOT NULL REFERENCES tournament(id) ON DELETE CASCADE,
	id_article		 	INTEGER NOT NULL,
	isdefault			SMALLINT DEFAULT 0,

	PRIMARY KEY(id_tournament, id_article)
);

CREATE OR REPLACE VIEW vw_tournamentarticle AS
	SELECT a.*, ta.id_tournament, ta.isdefault
	FROM tournament_article ta LEFT JOIN article a ON (ta.id_article = a.id); 

CREATE TABLE tournament_ttype(
	id_tournament 		INTEGER NOT NULL REFERENCES tournament(id) ON DELETE CASCADE,
	id_tournamenttype 	INTEGER NOT NULL,

	PRIMARY KEY(id_tournament, id_tournamenttype)
);

CREATE OR REPLACE VIEW vw_tournament_ttype AS
	SELECT *
	FROM tournament_ttype tt LEFT JOIN tournamenttype t ON (tt.id_tournamenttype = t.id);

CREATE TABLE registration(
	id					INTEGER NOT NULL,
	id_siteuser 		INTEGER REFERENCES siteuser(id) ON DELETE CASCADE,
	id_tournament 		INTEGER NOT NULL REFERENCES tournament(id) ON DELETE CASCADE,
	id_tournamenttype 	INTEGER NOT NULL,
	comment				TEXT,
	insdate				TIMESTAMP NOT NULL DEFAULT current_timestamp,

	PRIMARY KEY(id)	
);

CREATE TABLE registration_player(
	id					INTEGER NOT NULL,
	id_registration		INTEGER NOT NULL REFERENCES registration(id) ON DELETE CASCADE,
	name				VARCHAR(255) NOT NULL,
	email				VARCHAR(255),
	properties			TEXT,
	created_at			TIMESTAMP DEFAULT current_timestamp,

	PRIMARY KEY(id)	
);

CREATE OR REPLACE VIEW vw_registration AS
	SELECT r.*, rp.id AS player_id, rp.name, rp.email, rp.created_at, rp.properties
	FROM registration r INNER JOIN registration_player rp ON (r.id = rp.id_registration);

-----------------------------------------------------------------
-- Views
-----------------------------------------------------------------

-- EZT LEHET HOGY MÁSKÉPP KELL A PROJEKTBEN!!!

CREATE OR REPLACE VIEW vw_forummessage AS
	SELECT
		fm.*,
		p.name
	FROM
		forummessage fm LEFT JOIN
		siteuser p ON (fm.id_user = p.id);


CREATE OR REPLACE VIEW vw_articles_withfolder AS
	SELECT
		a.*,
		af.id_folder
	FROM
		article a LEFT JOIN
		articlefolder_article af ON (a.id = af.id_article AND a.langcode = af.langcode);

CREATE OR REPLACE VIEW vw_article_withfolder_forcombo AS
	SELECT
		f.id AS folderid,
		f.id_parent,
		f.name,
		a.id AS articleid,
		a.modifiedat AS moddate,
		CASE WHEN a.site_id NOTNULL THEN a.site_id ELSE f.site_id END,
		CASE WHEN a.langcode NOTNULL THEN a.langcode ELSE f.langcode END,
		CASE WHEN a.type NOTNULL THEN a.type ELSE f.type END,
		a.worktitle
	FROM
		article a LEFT JOIN
		articlefolder_article af ON (a.id = af.id_article) FULL OUTER JOIN
		article_folder f ON (f.id = af.id_folder);
